
package mapper;

import Utilities.DBConnection;
import bo.BUS_KhachHang;
import bo.BUS_SanPham;
import dto.DTO_HoaDon;
import dto.DTO_KhachHang;
import dto.DTO_SanPham;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MAP_HoaDon {

    public boolean themHD(DTO_HoaDon hd)
    {
        boolean flag = false;
        String qry = "insert into HoaDon(NgayLapHD,NgayGiaoHang,DiaChiGiaoHang,MaKH,TrangThai) values('"+hd.getNgayLapHD()+"','"+hd.getNgayGiaoHang()+"','"+hd.getDiachiGiaoHang()+"',"+hd.getMaKH()+",0)";
        int a = DBConnection.executeQueryString(qry);
        if(a == 1)
        {
            flag = true;
        }
        return flag;
    }
    public int LayMaHD(int MaKH) throws SQLException
    {
        String qry = "select top 1 * from HoaDon where MaKH = "+MaKH +" order by MaHD desc";
        ResultSet rs = DBConnection.getResultSet(qry);
        rs.next();
        int maHD = rs.getInt(1);
        return maHD;

    }
    public ArrayList<DTO_HoaDon> LoadDSHD(int TrangThai)
    {
        ArrayList<DTO_HoaDon> dshd = new ArrayList<DTO_HoaDon>();
        try {
            String qry = "select MaHD,NgayLapHD,NgayGiaoHang,HoTen,DiaChiGiaoHang,TrangThai from HoaDon h,KhachHang k where h.MaKH = k.MaKH and h.TrangThai = " + TrangThai;
            ResultSet rs = DBConnection.getResultSet(qry);
            while (rs != null && rs.next()) {
                DTO_HoaDon hd = new DTO_HoaDon();
                hd.setMaHD(rs.getInt(1));
                hd.setNgayLapHD(rs.getDate(2).toString());
                hd.setNgayGiaoHang(rs.getDate(3).toString());
                hd.setHoTen(rs.getString(4));
                hd.setDiachiGiaoHang(rs.getString(5));
                hd.setTrangThai(rs.getInt(6));

                dshd.add(hd);

            }
        } catch (SQLException ex) {
            Logger.getLogger(MAP_HoaDon.class.getName()).log(Level.SEVERE, null, ex);
        }
        return dshd;
    }
    public boolean CapNhatHD(DTO_HoaDon hd)
    {
        String qry = "update HoaDon set NgayGiaoHang = '"+hd.getNgayGiaoHang()+"' , DiaChiGiaoHang = '"+hd.getDiachiGiaoHang()+"' , TrangThai = "+hd.getTrangThai()+" where MaHD = "+hd.getMaHD();
        int flag = DBConnection.executeQueryString(qry);
        if(flag == 1)
        {
            return true;
        }
        return false;
    }
    public boolean XoaHD(int MaHD)
    {
        String qry = "delete from HoaDon where MaHD = "+MaHD;
        int flag = DBConnection.executeQueryString(qry);
        if(flag == 1)
        {
            return true;
        }
        return false;
    }
    public int TongDoanhThu() throws SQLException
    {
        int tong =0;
        String qry = "Select SUM((sp.GiaBan)*cthd.SoLuong) as tong from ChiTiet_HoaDon cthd , SanPham sp where sp.MaSP = cthd.MaSP";
        ResultSet rs = DBConnection.getResultSet(qry);
        if(rs!= null & rs.next())
        {
            tong = rs.getInt(1);
        }
        return tong;
    }
    public ArrayList<dto.DTO_SanPham> dsBanChay(int sl) throws SQLException
    {
        String qry = "Select Top "+sl+" sp.MaSP as ma ,sum (cthd.SoLuong) as tongsl,sum (sp.GiaBan*cthd.SoLuong )as tong from ChiTiet_HoaDon cthd , SanPham sp where sp.MaSP = cthd.MaSP Group By sp.MaSP Order By tong  DESC ";
        ResultSet rs = DBConnection.getResultSet(qry);
        ArrayList<dto.DTO_SanPham> ds = new ArrayList<DTO_SanPham>();
        
        while(rs!=null & rs.next())
        {
            dto.DTO_SanPham sp = new DTO_SanPham();
            bo.BUS_SanPham bus = new BUS_SanPham();
            sp.setMaSP(rs.getInt(1));
            sp = bus.LayThongTinSanPham(sp.getMaSP());
            sp.setSoluongban(rs.getInt(2));
            sp.setDoanhthu(rs.getInt(3));
            ds.add(sp);
        }
        return ds;
    }
    public int tongsanphamban() throws SQLException
    {
         int tong =0;
        String qry = "Select SUM (cthd.SoLuong) as tong from ChiTiet_HoaDon cthd , SanPham sp where sp.MaSP = cthd.MaSP";
        ResultSet rs = DBConnection.getResultSet(qry);
        if(rs!= null & rs.next())
        {
            tong = rs.getInt(1);
        }
        return tong;
    }
    public ArrayList<dto.DTO_KhachHang> dskhahhang(int sl) throws SQLException
    {
        ArrayList<dto.DTO_KhachHang> ds = new ArrayList<dto.DTO_KhachHang>();
        String qry = "select top "+sl+" MaKH from HoaDon group by MaKH order by COUNT(MaHD) desc";
        ResultSet rs = DBConnection.getResultSet(qry);
        bo.BUS_KhachHang bus = new BUS_KhachHang();
        while(rs!= null & rs.next())
        {
            DTO_KhachHang kh = new DTO_KhachHang();
            kh.setMaKH( rs.getInt(1));
            kh = bus.LayThongTinKhachHang(kh.getMaKH());
            ds.add(kh);
        }
        return ds;
    }
   
}
